﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace weikebuluo.DBUtility
{
    /// <summary>
    /// 实体类映射数据库字段,根据实体自动创建SQL语句及相关参数
    /// （增、删、改、查,只适应OO型结构，后期修改数据表结构时，不用修改相关SQL语句，方便维护）
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DataCreator<T> where T : class,new()
    {
        /// <summary>
        /// 创建T实体设置参数
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public SqlParameter[] CreateParameter(T model)
        {
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;
            List<SqlParameter> listpara = new List<SqlParameter>();
            SqlParameter[] paras = new SqlParameter[pislen - 1];
            for (int i = 0; i < pislen; i++)
            {
                if (pis[i].Name == "TableName")
                {
                    continue;
                }
                listpara.Add(new SqlParameter(string.Format("@{0}", pis[i].Name), TypeConvert.GetSqlDbType(pis[i].PropertyType)));

            }
            paras = listpara.ToArray();
            int j = 0; object obj = null;
            for (int i = 0; i < pislen; i++)
            {
                if (pis[i].Name == "TableName")
                {
                    continue;
                }
                obj = pis[i].GetValue(model, null);
                if (pis[i].PropertyType.Name == "DateTime")
                {
                    if (obj.ToString() == "0001-1-1 0:00:00")
                    {
                        obj = null;
                    }
                }
                paras[j].Value = obj;
                j++;
            }
            return paras;
        }

        /// <summary>
        /// 创建添加SQL语句(设置参数)
        /// </summary>
        public string CreateInsertSql(T model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strParameter = new StringBuilder();
            strSql.Append("insert into {0} (");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;
            string _tablename = "";
            for (int i = 0; i < pislen; i++)
            {
                if (pis[i].Name == "TableName")
                {
                    _tablename = pis[i].GetValue(model, null).ToString();
                    continue;
                }
                strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份 
                strParameter.Append("@" + pis[i].Name + ","); //构造参数SQL语句
            }
            strSql = strSql.Replace(",", ")", strSql.Length - 1, 1);
            strParameter = strParameter.Replace(",", ")", strParameter.Length - 1, 1);
            strSql.Append(" values (");
            strSql.Append(strParameter + ";");
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }
        public string CreateInsertSql(T model, string _tablename)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strParameter = new StringBuilder();
            strSql.Append("insert into {0} (");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;

            for (int i = 0; i < pislen; i++)
            {
                strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份 
                strParameter.Append("@" + pis[i].Name + ","); //构造参数SQL语句
            }
            strSql = strSql.Replace(",", ")", strSql.Length - 1, 1);
            strParameter = strParameter.Replace(",", ")", strParameter.Length - 1, 1);
            strSql.Append(" values (");
            strSql.Append(strParameter + ";");
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }
        public string CreateInsertSql(T model, string _tablename, string identity)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strParameter = new StringBuilder();
            strSql.Append("insert into {0} (");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;

            for (int i = 0; i < pislen; i++)
            {
                if (pis[i].Name.ToLower() == identity.ToLower())
                {
                    continue;
                }
                strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份 
                strParameter.Append("@" + pis[i].Name + ","); //构造参数SQL语句
            }
            strSql = strSql.Replace(",", ")", strSql.Length - 1, 1);
            strParameter = strParameter.Replace(",", ")", strParameter.Length - 1, 1);
            strSql.Append(" values (");
            strSql.Append(strParameter + ";");
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }

        public string CreateInsertSql(T model, string _tablename, string[] filter)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strParameter = new StringBuilder();
            strSql.Append("insert into {0} (");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;

            for (int i = 0; i < pislen; i++)
            {
                if (filter.Count(x => x.ToLower().Contains(pis[i].Name.ToLower())) > 0)
                {
                    continue;
                }
                strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份 
                strParameter.Append("@" + pis[i].Name + ","); //构造参数SQL语句
            }
            strSql = strSql.Replace(",", ")", strSql.Length - 1, 1);
            strParameter = strParameter.Replace(",", ")", strParameter.Length - 1, 1);
            strSql.Append(" values (");
            strSql.Append(strParameter + ";");
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 创建删除SQL语句
        /// </summary>
        public string CreateDeleteSql(string strTable, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("delete from {0} ", strTable);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            return strSql.ToString();
        }

        /// <summary>
        /// 创建更新SQL语句（）
        /// </summary>
        public string CreateUpdateSql(T model, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update {0} set ");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;
            string _tablename = "";
            for (int i = 0; i < pislen; i++)
            {
                if (pis[i].Name == "TableName")
                {
                    _tablename = pis[i].GetValue(model, null).ToString();
                    continue;
                }
                strSql.Append(pis[i].Name + "=@" + pis[i].Name + ",");
            }
            strSql = strSql.Replace(",", "", strSql.Length - 1, 1);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }
        public string CreateUpdateSql(T model, string _tablename, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update {0} set ");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;

            for (int i = 0; i < pislen; i++)
            {
                strSql.Append(pis[i].Name + "=@" + pis[i].Name + ",");
            }
            strSql = strSql.Replace(",", "", strSql.Length - 1, 1);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }
        public string CreateUpdateSql(T model, string _tablename, string identity, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update {0} set ");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;

            for (int i = 0; i < pislen; i++)
            {
                if (pis[i].Name.ToLower() == identity.ToLower())
                {
                    continue;
                }
                strSql.Append(pis[i].Name + "=@" + pis[i].Name + ",");
            }
            strSql = strSql.Replace(",", "", strSql.Length - 1, 1);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }

        public string CreateUpdateSql(T model, string _tablename, string[] filter, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update {0} set ");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;

            for (int i = 0; i < pislen; i++)
            {
                if (filter.Count(x => x.ToLower().Contains(pis[i].Name.ToLower())) > 0)
                    continue;
                strSql.Append(pis[i].Name + "=@" + pis[i].Name + ",");
            }
            strSql = strSql.Replace(",", "", strSql.Length - 1, 1);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            if (_tablename != "")
            {
                return string.Format(strSql.ToString(), _tablename);
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 创建查询SQL语句
        /// </summary>
        public string CreateSelectSql(T model, string strWhere, string strOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;
            string _tablename = "";
            for (int i = 0; i < pislen; i++)
            {
                if (pis[i].Name == "TableName")
                {
                    _tablename = pis[i].GetValue(model, null).ToString();
                    continue;
                }
                strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份 

            }
            strSql = strSql.Replace(",", "", strSql.Length - 1, 1);
            strSql.Append(" from " + _tablename);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            if (!string.IsNullOrEmpty(strOrder))
            {
                strSql.Append(" order by " + strOrder);
            }
            if (_tablename != "")
            {
                return strSql.ToString();
            }
            else
            {
                return "";
            }
        }
        public string CreateSelectSql(T model, string _tablename, string strWhere, string strOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int pislen = pis.Length;

            for (int i = 0; i < pislen; i++)
            {
                strSql.Append(pis[i].Name + ","); //构造SQL语句前半部份 
            }
            strSql = strSql.Replace(",", "", strSql.Length - 1, 1);
            strSql.Append(" from " + _tablename);
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            if (!string.IsNullOrEmpty(strOrder))
            {
                strSql.Append(" order by " + strOrder);
            }
            if (_tablename != "")
            {
                return strSql.ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="model"></param>
        /// <param name="ds"></param>
        /// <returns></returns>
        public T GetModel(T model, DataSet ds)
        {
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            foreach (PropertyInfo pi in pis)
            {
                if (pi.Name == "TableName")
                {
                    continue;
                }
                if (ds.Tables[0].Rows[0][pi.Name] != null && ds.Tables[0].Rows[0][pi.Name].ToString() != "")
                {
                    pi.SetValue(model, ds.Tables[0].Rows[0][pi.Name], null);
                }
            }
            return model;
        }
        public T GetModel(T model, IDataReader dr)
        {
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            int iIndex;
            foreach (PropertyInfo pi in pis)
            {
                if (pi.Name == "TableName")
                {
                    continue;
                }
                iIndex = dr.GetOrdinal(pi.Name);
                if (!dr.IsDBNull(iIndex))
                {
                    pi.SetValue(model, dr.GetValue(iIndex), null);
                }
            }
            return model;
        }
        public List<T> GetList(T model, IDataReader dr)
        {
            List<T> list = new List<T>();
            while (dr.Read())
            {
                list.Add(GetModel(model, dr));
            }
            return list;
        }

        /// <summary>
        /// 实体赋值
        /// </summary>
        /// <param name="model">实体</param>
        /// <param name="dicValue">值</param>
        /// <param name="UpperAndLower">区分大小写</param>
        public void SetModelValue(T model, Dictionary<string, string> dicValue, bool UpperAndLower)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            if (!UpperAndLower)
            {
                foreach (string s in dicValue.Keys)
                {
                    if (!dic.ContainsKey(s.ToLower()))
                    {
                        dic.Add(s.ToLower(), dicValue[s]);
                    }
                }
            }
            else
            {
                dic = dicValue;
            }
            Type t = typeof(T);
            PropertyInfo[] pis = t.GetProperties(BindingFlags.Instance | BindingFlags.DeclaredOnly | BindingFlags.Public);
            foreach (PropertyInfo pi in pis)
            {
                Type property = pi.PropertyType;
                if (UpperAndLower)
                {
                    if (dic.ContainsKey(pi.Name))
                    {
                        if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            pi.SetValue(model, TypeConvert.TypeRransition(pi.PropertyType.GetGenericArguments()[0], dic[pi.Name]), null);
                        }
                        else
                        {
                            pi.SetValue(model, TypeConvert.TypeRransition(pi.PropertyType, dic[pi.Name]), null);
                        }
                    }
                }
                else
                {
                    if (dic.ContainsKey(pi.Name.ToLower()))
                    {
                        if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            pi.SetValue(model, TypeConvert.TypeRransition(pi.PropertyType.GetGenericArguments()[0], dic[pi.Name.ToLower()]), null);
                        }
                        else
                        {
                            pi.SetValue(model, TypeConvert.TypeRransition(pi.PropertyType, dic[pi.Name.ToLower()]), null);
                        }
                    }
                }
            }
        }
    }
}
